﻿/**********************************************************
 ●项目：Smartkernel
 ●公司：智能核心信息技术研发中心
 ●作者：曹艳白
 ●邮箱：caoyanbai@gmail.com
 ●创建：2008-08-10
 ●版本：1.0
**********************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Transactions;
using Smartkernel.Attributes;
using Smartkernel.Exceptions;
using Smartkernel.Extensions;
namespace Smartkernel.Data
{
    /// <summary>
    /// 1.数据库操作功能，以及部分OR和RO映射功能的封装。
    /// 2.这里暂时不考虑LINQ方式的OR映射，因为深入研究LINQ实现方式之后，觉得目前应用还不成熟，暂不适合于在大型项目中使用。
    /// 3.越大型的项目，数据库部分所占比重越大，越不适合完全的面向对象，关系型向对象的映射会降低性能。因此，框架的数据库部分还是采用传统的DataSet方式（支持类型化数据集）。
    /// 4.在获取单个实体时，框架使用了反射以减少客户端代码的数量。单个实体一般会被客户端缓存。所以反射的映像不是很严重。
    /// 5.事务以及分布式事务的处理，框架建议使用TransactionScope方式，这样可以保证方法的独立，不需要把要组成事务的方法代码也放在一起。
    /// 6.服务器需要启用MSDTC来支持分布式事务，还需注意Sql Server 2000不支持TransactionScope，所以只能用ADO.NET中的事务。
    /// 7.因为数据库操作往往是性能的瓶颈，所以本类会在将来的版本中不断提高性能。因此代码可能会越来越难于理解。降低部分清晰度以提高性能。
    /// </summary>
    public static partial class SmartDatabase
    {
        #region ExecuteNonQuery方法
        /// <summary>
        /// 执行INSERT、UPDATE、DELETE以及不返回数据集的存储过程
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL命令或存储过程名</param>
        /// <param name="parameters">参数数组</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, string sentence, DbParameter[] parameters, SmartDatabaseType smartDatabaseType)
        {
            DbProviderFactory factory = GetDbProviderFactory(smartDatabaseType);
            using (DbConnection dbConnection = factory.CreateConnection())
            {
                dbConnection.ConnectionString = connectionString;
                using (DbCommand dbCommand = dbConnection.CreateCommand())
                {
                    dbCommand.CommandText = sentence;
                    //操作超时时间，目前设置为3分钟
                    dbCommand.CommandTimeout = 180;
                    if (parameters != null)
                    {
                        dbCommand.Parameters.AddRange(parameters);
                    }
                    dbConnection.Open();
                    return dbCommand.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 执行INSERT、UPDATE、DELETE以及不返回数据集的存储过程
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL命令或存储过程名</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, string sentence, SmartDatabaseType smartDatabaseType)
        {
            return ExecuteNonQuery(connectionString, sentence, null, smartDatabaseType);
        }
        /// <summary>
        /// 执行INSERT、UPDATE、DELETE以及不返回数据集的存储过程，默认是SqlServer数据库
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL命令或存储过程名</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, string sentence, DbParameter[] parameters)
        {
            return ExecuteNonQuery(connectionString, sentence, parameters, SmartDatabaseType.SqlServer);
        }
        /// <summary>
        /// 执行INSERT、UPDATE、DELETE以及不返回数据集的存储过程，默认是SqlServer数据库
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL命令或存储过程名</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, string sentence)
        {
            return ExecuteNonQuery(connectionString, sentence, null, SmartDatabaseType.SqlServer);
        }
        #endregion

        #region ExecuteReader方法
        /// <summary>
        /// 执行SELECT以及返回数据集的存储过程，返回读取器。读取器关闭时，连接会自动关闭。不建议在BS项目中使用，因为传统的DataSet方式要更容易控制缓存。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL命令或存储过程名</param>
        /// <param name="parameters">参数数组</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        /// <returns>读取器</returns>
        public static DbDataReader ExecuteReader(string connectionString, string sentence, DbParameter[] parameters, SmartDatabaseType smartDatabaseType)
        {
            DbProviderFactory factory = GetDbProviderFactory(smartDatabaseType);
            DbConnection dbConnection = factory.CreateConnection();
            dbConnection.ConnectionString = connectionString;
            try
            {
                using (DbCommand dbCommand = dbConnection.CreateCommand())
                {
                    dbCommand.CommandText = sentence;
                    if (parameters != null)
                    {
                        dbCommand.Parameters.AddRange(parameters);
                    }
                    dbConnection.Open();
                    return dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            catch (Exception exception)
            {
                //如果发生异常才关闭连接，然后抛出异常信息。如果未发生异常，不能关闭连接，连接是由客户端程序员控制。
                dbConnection.Close();
                throw exception;
            }
        }
        /// <summary>
        /// 执行SELECT以及返回数据集的存储过程，返回读取器。读取器关闭时，连接会自动关闭。不建议在BS项目中使用，因为传统的DataSet方式要更容易控制缓存。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL命令或存储过程名</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        /// <returns>读取器</returns>
        public static DbDataReader ExecuteReader(string connectionString, string sentence, SmartDatabaseType smartDatabaseType)
        {
            return ExecuteReader(connectionString, sentence, null, smartDatabaseType);
        }
        /// <summary>
        /// 执行SELECT以及返回数据集的存储过程，返回读取器。读取器关闭时，连接会自动关闭。不建议在BS项目中使用，因为传统的DataSet方式要更容易控制缓存。默认是SqlServer数据库
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL命令或存储过程名</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>读取器</returns>
        public static DbDataReader ExecuteReader(string connectionString, string sentence, DbParameter[] parameters)
        {
            return ExecuteReader(connectionString, sentence, parameters, SmartDatabaseType.SqlServer);
        }
        /// <summary>
        /// 执行SELECT以及返回数据集的存储过程，返回读取器。读取器关闭时，连接会自动关闭。不建议在BS项目中使用，因为传统的DataSet方式要更容易控制缓存。默认是SqlServer数据库
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL命令或存储过程名</param>
        /// <returns>读取器</returns>
        public static DbDataReader ExecuteReader(string connectionString, string sentence)
        {
            return ExecuteReader(connectionString, sentence, null, SmartDatabaseType.SqlServer);
        }
        #endregion

        #region ExecuteScalar方法
        /// <summary>
        /// 返回数据集的第一行第一列。数据库中为Null或空，都返回Null
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL命令或存储过程名</param>
        /// <param name="parameters">参数数组</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        /// <returns>数据集的第一行第一列</returns>
        public static object ExecuteScalar(string connectionString, string sentence, DbParameter[] parameters, SmartDatabaseType smartDatabaseType)
        {
            object result = null;
            DbProviderFactory factory = GetDbProviderFactory(smartDatabaseType);
            using (DbConnection dbConnection = factory.CreateConnection())
            {
                dbConnection.ConnectionString = connectionString;
                using (DbCommand dbCommand = dbConnection.CreateCommand())
                {
                    dbCommand.CommandText = sentence;
                    if (parameters != null)
                    {
                        dbCommand.Parameters.AddRange(parameters);
                    }
                    dbConnection.Open();
                    result = dbCommand.ExecuteScalar();
                }
            }
            //如果返回的是DBNull类型或者是空，则返回null
            if (result == DBNull.Value || result.ToString().Trim().Length == 0)
            {
                result = null;
            }
            return result;
        }
        /// <summary>
        /// 返回数据集的第一行第一列。数据库中为Null或空，都返回Null
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL命令或存储过程名</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        /// <returns>数据集的第一行第一列</returns>
        public static object ExecuteScalar(string connectionString, string sentence, SmartDatabaseType smartDatabaseType)
        {
            return ExecuteScalar(connectionString, sentence, null, smartDatabaseType);
        }
        /// <summary>
        /// 返回数据集的第一行第一列。数据库中为Null或空，都返回Null。默认是SqlServer数据库
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL命令或存储过程名</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>数据集的第一行第一列</returns>
        public static object ExecuteScalar(string connectionString, string sentence, DbParameter[] parameters)
        {
            return ExecuteScalar(connectionString, sentence, parameters, SmartDatabaseType.SqlServer);
        }
        /// <summary>
        /// 返回数据集的第一行第一列。数据库中为Null或空，都返回Null。默认是SqlServer数据库
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL命令或存储过程名</param>
        /// <returns>数据集的第一行第一列</returns>
        public static object ExecuteScalar(string connectionString, string sentence)
        {
            return ExecuteScalar(connectionString, sentence, null, SmartDatabaseType.SqlServer);
        }
        #endregion

        #region Fill方法
        /// <summary>
        /// 填充数据集（数据集不是作为返回值，而是作为参数传入，这个决策主要是为了可以使用强类型的数据集。传入强类型的数据集也可以使用）
        /// </summary>
        /// <param name="dataSet">数据集（可以是强类型的数据集）</param>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL语句或存储过程名</param>
        /// <param name="parameters">参数数组</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        public static void Fill(string connectionString, string sentence, DataSet dataSet, DbParameter[] parameters, SmartDatabaseType smartDatabaseType)
        {
            DbProviderFactory factory = GetDbProviderFactory(smartDatabaseType);
            DbConnection dbConnection = factory.CreateConnection();
            dbConnection.ConnectionString = connectionString;

            using (DbCommand dbCommand = dbConnection.CreateCommand())
            {
                dbCommand.CommandText = sentence;
                dbCommand.CommandTimeout = 180;
                if (parameters != null)
                {
                    dbCommand.Parameters.AddRange(parameters);
                }
                using (DbDataAdapter dbDataAdapter = factory.CreateDataAdapter())
                {
                    dbDataAdapter.SelectCommand = dbCommand;
                    dbDataAdapter.Fill(dataSet);
                }
            }
        }
        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <param name="dataSet">数据集（可以是强类型的数据集）</param>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL语句或存储过程名</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        public static void Fill(string connectionString, string sentence, DataSet dataSet, SmartDatabaseType smartDatabaseType)
        {
            Fill(connectionString, sentence, dataSet, null, smartDatabaseType);
        }
        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <param name="dataTable">数据表（可以是强类型的数据表）</param>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL语句或存储过程名</param>
        /// <param name="parameters">参数数组</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        public static void Fill(string connectionString, string sentence, DataTable dataTable, DbParameter[] parameters, SmartDatabaseType smartDatabaseType)
        {
            DbProviderFactory factory = GetDbProviderFactory(smartDatabaseType);
            DbConnection dbConnection = factory.CreateConnection();
            dbConnection.ConnectionString = connectionString;

            using (DbCommand dbCommand = dbConnection.CreateCommand())
            {
                dbCommand.CommandText = sentence;
                dbCommand.CommandTimeout = 180;
                if (parameters != null)
                {
                    dbCommand.Parameters.AddRange(parameters);
                }
                using (DbDataAdapter dbDataAdapter = factory.CreateDataAdapter())
                {
                    dbDataAdapter.SelectCommand = dbCommand;
                    dbDataAdapter.Fill(dataTable);
                }
            }
        }
        /// <summary>
        /// 填充数据集
        /// </summary>
        /// <param name="dataTable">数据表（可以是强类型的数据表）</param>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL语句或存储过程名</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        public static void Fill(string connectionString, string sentence, DataTable dataTable, SmartDatabaseType smartDatabaseType)
        {
            Fill(connectionString, sentence, dataTable, null, smartDatabaseType);
        }
        /// <summary>
        /// 填充数据集，默认是SqlServer数据库
        /// </summary>
        /// <param name="dataSet">数据集（可以是强类型的数据集）</param>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL语句或存储过程名</param>
        /// <param name="parameters">参数数组</param>
        public static void Fill(string connectionString, string sentence, DataSet dataSet, DbParameter[] parameters)
        {
            Fill(connectionString, sentence, dataSet, parameters, SmartDatabaseType.SqlServer);
        }
        /// <summary>
        /// 填充数据集，默认是SqlServer数据库
        /// </summary>
        /// <param name="dataSet">数据集（可以是强类型的数据集）</param>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL语句或存储过程名</param>
        public static void Fill(string connectionString, string sentence, DataSet dataSet)
        {
            Fill(connectionString, sentence, dataSet, null, SmartDatabaseType.SqlServer);
        }
        /// <summary>
        /// 填充数据集，默认是SqlServer数据库
        /// </summary>
        /// <param name="dataTable">数据表（可以是强类型的数据表）</param>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL语句或存储过程名</param>
        /// <param name="parameters">参数数组</param>
        public static void Fill(string connectionString, string sentence, DataTable dataTable, DbParameter[] parameters)
        {
            Fill(connectionString, sentence, dataTable, parameters, SmartDatabaseType.SqlServer);
        }
        /// <summary>
        /// 填充数据集，默认是SqlServer数据库
        /// </summary>
        /// <param name="dataTable">数据表（可以是强类型的数据表）</param>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sentence">SQL语句或存储过程名</param>
        public static void Fill(string connectionString, string sentence, DataTable dataTable)
        {
            Fill(connectionString, sentence, dataTable, null, SmartDatabaseType.SqlServer);
        }
        #endregion
    }
    public static partial class SmartDatabase
    {
        #region RO映射
        /// <summary>
        /// OR映射工具，将数据行映射为对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="dataRow">数据（R）</param>
        /// <param name="smartMappingType">映射的类型</param>
        /// <returns>对象（O）</returns>
        public static T MappingRO<T>(DataRow dataRow, SmartMappingType smartMappingType)
        {
            T item = Activator.CreateInstance<T>();
            MappingRO<T>(dataRow, smartMappingType, ref item);
            return item;
        }
        /// <summary>
        /// OR映射工具，将数据行映射为对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="dataRow">数据（R）</param>
        /// <param name="smartMappingType">映射的类型</param>
        /// <param name="item">实体的实例</param>
        public static void MappingRO<T>(DataRow dataRow, SmartMappingType smartMappingType, ref T item)
        {
            Type type = item.GetType();
            switch (smartMappingType)
            {
                case SmartMappingType.Attribute:
                    {
                        #region 根据特性标识映射
                        //设置属性
                        foreach (PropertyInfo propertyInfo in SmartReflection.GetPropertiesWithAttribute<SmartColumnMappingAttribute>(type))
                        {
                            SmartColumnMappingAttribute attribute = (SmartColumnMappingAttribute)Attribute.GetCustomAttribute(propertyInfo, typeof(SmartColumnMappingAttribute), false);

                            if (dataRow.Table.Columns.Contains(attribute.ColumnName))
                            {
                                object value = dataRow[attribute.ColumnName];
                                //数据库中的字段为Null或空时
                                if (value == DBNull.Value || value.ToString().Trim().Length == 0)
                                {
                                    //如果有默认值设置，则使用默认值，否则保留初始化时的原始值
                                    if (attribute.DefaultValue != null)
                                    {
                                        propertyInfo.SetValue(item, attribute.DefaultValue, null);
                                    }
                                }
                                else
                                {
                                    propertyInfo.SetValue(item, SmartReflection.ConvertTo(value.ToString().Trim(), propertyInfo.PropertyType), null);
                                }
                            }
                            else
                            {
                                if (attribute.DefaultValue != null)
                                {
                                    propertyInfo.SetValue(item, attribute.DefaultValue, null);
                                }
                            }
                        }
                        //设置字段
                        foreach (FieldInfo fieldInfo in SmartReflection.GetFieldsWithAttribute<SmartColumnMappingAttribute>(type))
                        {
                            SmartColumnMappingAttribute attribute = (SmartColumnMappingAttribute)Attribute.GetCustomAttribute(fieldInfo, typeof(SmartColumnMappingAttribute), false);

                            if (dataRow.Table.Columns.Contains(attribute.ColumnName))
                            {
                                object value = dataRow[attribute.ColumnName];
                                //数据库中的字段为Null或空时
                                if (value == DBNull.Value || value.ToString().Trim().Length == 0)
                                {
                                    //如果有默认值设置，则使用默认值，否则保留初始化时的原始值
                                    if (attribute.DefaultValue != null)
                                    {
                                        fieldInfo.SetValue(item, attribute.DefaultValue);
                                    }
                                }
                                else
                                {
                                    fieldInfo.SetValue(item, SmartReflection.ConvertTo(value.ToString().Trim(), fieldInfo.FieldType));
                                }
                            }
                            else
                            {
                                if (attribute.DefaultValue != null)
                                {
                                    fieldInfo.SetValue(item, attribute.DefaultValue);
                                }
                            }
                        }
                        #endregion
                    }
                    break;
                case SmartMappingType.Field:
                    {
                        #region 根据字段映射
                        //设置字段
                        foreach (FieldInfo fieldInfo in SmartReflection.GetFields(type))
                        {
                            if (dataRow.Table.Columns.Contains(fieldInfo.Name))
                            {
                                object value = dataRow[fieldInfo.Name];
                                //如果数据库中的值为Null或为空，则保持初始化时的默认值
                                if (value != DBNull.Value && value.ToString().Trim().Length > 0)
                                {
                                    fieldInfo.SetValue(item, SmartReflection.ConvertTo(value.ToString().Trim(), fieldInfo.FieldType));
                                }
                            }
                        }
                        #endregion
                    }
                    break;
                case SmartMappingType.Property:
                    {
                        #region 根据属性映射
                        //设置属性
                        foreach (PropertyInfo propertyInfo in SmartReflection.GetProperties(type))
                        {
                            if (dataRow.Table.Columns.Contains(propertyInfo.Name))
                            {
                                object value = dataRow[propertyInfo.Name];
                                //如果数据库中的值为Null或为空，则保持初始化时的默认值
                                if (value != DBNull.Value && value.ToString().Trim().Length > 0)
                                {
                                    propertyInfo.SetValue(item, SmartReflection.ConvertTo(value.ToString().Trim(), propertyInfo.PropertyType), null);
                                }
                            }
                        }
                        #endregion
                    }
                    break;
            }
        }
        /// <summary>
        /// OR映射工具，将数据行映射为对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="dataRowCollection">数据（R）</param>
        /// <param name="smartMappingType">映射的类型</param>
        /// <returns>对象列表（O）</returns>
        public static List<T> MappingROList<T>(DataRowCollection dataRowCollection, SmartMappingType smartMappingType)
        {
            List<T> list = new List<T>(dataRowCollection.Count);
            for (int i = 0; i < dataRowCollection.Count; i++)
            {
                list.Add(MappingRO<T>(dataRowCollection[i], smartMappingType));
            }
            return list;
        }
        /// <summary>
        /// OR映射工具，将数据行映射为对象，数据不是一行，是多行，适用于纵向数据的情况
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="dataTable">纵向数据（R）</param>
        /// <param name="smartMappingType">映射的类型</param>
        /// <param name="item">实体的实例</param>
        /// <param name="keyColumn">作为键的列</param>
        /// <param name="valueColumn">作为值的列</param>
        public static void MappingROVerticalData<T>(DataTable dataTable, SmartMappingType smartMappingType, ref T item, string keyColumn, string valueColumn)
        {
            Type type = item.GetType();
            switch (smartMappingType)
            {
                case SmartMappingType.Attribute:
                    {
                        #region 根据特性标识映射
                        //设置属性
                        foreach (PropertyInfo propertyInfo in SmartReflection.GetPropertiesWithAttribute<SmartColumnMappingAttribute>(type))
                        {
                            SmartColumnMappingAttribute attribute = (SmartColumnMappingAttribute)Attribute.GetCustomAttribute(propertyInfo, typeof(SmartColumnMappingAttribute), false);
                            object value = null;
                            if (FindValueInVerticalData(dataTable, keyColumn, valueColumn, attribute.ColumnName, out value))
                            {
                                //数据库中的字段为Null或空时
                                if (value == DBNull.Value || value.ToString().Trim().Length == 0)
                                {
                                    //如果有默认值设置，则使用默认值，否则保留初始化时的原始值
                                    if (attribute.DefaultValue != null)
                                    {
                                        propertyInfo.SetValue(item, attribute.DefaultValue, null);
                                    }
                                }
                                else
                                {
                                    propertyInfo.SetValue(item, SmartReflection.ConvertTo(value.ToString().Trim(), propertyInfo.PropertyType), null);
                                }
                            }
                            else
                            {
                                if (attribute.DefaultValue != null)
                                {
                                    propertyInfo.SetValue(item, attribute.DefaultValue, null);
                                }
                            }
                        }
                        //设置字段
                        foreach (FieldInfo fieldInfo in SmartReflection.GetFieldsWithAttribute<SmartColumnMappingAttribute>(type))
                        {
                            SmartColumnMappingAttribute attribute = (SmartColumnMappingAttribute)Attribute.GetCustomAttribute(fieldInfo, typeof(SmartColumnMappingAttribute), false);

                            object value = null;
                            if (FindValueInVerticalData(dataTable, keyColumn, valueColumn, attribute.ColumnName, out value))
                            {
                                //数据库中的字段为Null或空时
                                if (value == DBNull.Value || value.ToString().Trim().Length == 0)
                                {
                                    //如果有默认值设置，则使用默认值，否则保留初始化时的原始值
                                    if (attribute.DefaultValue != null)
                                    {
                                        fieldInfo.SetValue(item, attribute.DefaultValue);
                                    }
                                }
                                else
                                {
                                    fieldInfo.SetValue(item, SmartReflection.ConvertTo(value.ToString().Trim(), fieldInfo.FieldType));
                                }
                            }
                            else
                            {
                                if (attribute.DefaultValue != null)
                                {
                                    fieldInfo.SetValue(item, attribute.DefaultValue);
                                }
                            }
                        }
                        #endregion
                    }
                    break;
                case SmartMappingType.Field:
                    {
                        #region 根据字段映射
                        //设置字段
                        foreach (FieldInfo fieldInfo in SmartReflection.GetFields(type))
                        {
                            object value = null;
                            if (FindValueInVerticalData(dataTable, keyColumn, valueColumn, fieldInfo.Name, out value))
                            {
                                //如果数据库中的值为Null或为空，则保持初始化时的默认值
                                if (value != DBNull.Value && value.ToString().Trim().Length > 0)
                                {
                                    fieldInfo.SetValue(item, SmartReflection.ConvertTo(value.ToString().Trim(), fieldInfo.FieldType));
                                }
                            }
                        }
                        #endregion
                    }
                    break;
                case SmartMappingType.Property:
                    {
                        #region 根据属性映射
                        //设置属性
                        foreach (PropertyInfo propertyInfo in SmartReflection.GetProperties(type))
                        {
                            object value = null;
                            if (FindValueInVerticalData(dataTable, keyColumn, valueColumn, propertyInfo.Name, out value))
                            {
                                //如果数据库中的值为Null或为空，则保持初始化时的默认值
                                if (value != DBNull.Value && value.ToString().Trim().Length > 0)
                                {
                                    propertyInfo.SetValue(item, SmartReflection.ConvertTo(value.ToString().Trim(), propertyInfo.PropertyType), null);
                                }
                            }
                        }
                        #endregion
                    }
                    break;
            }
        }
        #endregion

        #region OR映射
        /// <summary>
        /// 根据映射的类型选择不同的映射方式
        /// </summary>
        /// <param name="item">实体对象</param>
        /// <param name="smartMappingType">映射类型</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        /// <returns>参数列表</returns>
        public static List<DbParameter> MappingOR(object item, SmartMappingType smartMappingType, SmartDatabaseType smartDatabaseType)
        {
            Dictionary<string, object> list = new Dictionary<string, object>();
            Type type = item.GetType();
            switch (smartMappingType)
            {
                case SmartMappingType.Attribute:
                    {
                        #region 根据特性标识映射
                        //设置属性
                        foreach (PropertyInfo propertyInfo in SmartReflection.GetPropertiesWithAttribute<SmartColumnMappingAttribute>(type))
                        {
                            SmartColumnMappingAttribute attribute = (SmartColumnMappingAttribute)Attribute.GetCustomAttribute(propertyInfo, typeof(SmartColumnMappingAttribute), false);
                            object value = propertyInfo.GetValue(item, null);
                            list.Add(CreateParameterName(attribute.ColumnName, smartDatabaseType), value);
                        }
                        //设置字段
                        foreach (FieldInfo fieldInfo in SmartReflection.GetFieldsWithAttribute<SmartColumnMappingAttribute>(type))
                        {
                            SmartColumnMappingAttribute attribute = (SmartColumnMappingAttribute)Attribute.GetCustomAttribute(fieldInfo, typeof(SmartColumnMappingAttribute), false);
                            object value = fieldInfo.GetValue(item);
                            list.Add(CreateParameterName(attribute.ColumnName, smartDatabaseType), value);
                        }
                        #endregion
                    }
                    break;
                case SmartMappingType.Field:
                    {
                        #region 根据字段映射
                        //设置字段
                        foreach (FieldInfo fieldInfo in SmartReflection.GetFields(type))
                        {
                            object value = fieldInfo.GetValue(item);
                            list.Add(CreateParameterName(fieldInfo.Name, smartDatabaseType), value);
                        }
                        #endregion
                    }
                    break;
                case SmartMappingType.Property:
                    {
                        #region 根据属性映射
                        //设置属性
                        foreach (PropertyInfo propertyInfo in SmartReflection.GetProperties(type))
                        {
                            object value = propertyInfo.GetValue(item, null);
                            list.Add(CreateParameterName(propertyInfo.Name, smartDatabaseType), value);
                        }
                        #endregion
                    }
                    break;
            }
            return MappingOR(list, smartDatabaseType);
        }
        /// <summary>
        /// 根据映射的类型选择不同的映射方式，默认是SqlServer数据库
        /// </summary>
        /// <param name="item">实体对象</param>
        /// <param name="smartMappingType">映射类型</param>
        /// <returns>参数列表</returns>
        public static List<DbParameter> MappingOR(object item, SmartMappingType smartMappingType)
        {
            return MappingOR(item, smartMappingType, SmartDatabaseType.SqlServer);
        }
        /// <summary>
        /// 根据键值对生成参数数组
        /// </summary>
        /// <param name="list">键值对</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        /// <returns>返回参数列表</returns>
        public static List<DbParameter> MappingOR(Dictionary<string, object> list, SmartDatabaseType smartDatabaseType)
        {
            List<DbParameter> parameters = new List<DbParameter>(list.Count);

            var enumerator = list.GetEnumerator();

            while (enumerator.MoveNext())
            {
                string key = enumerator.Current.Key;
                object value = enumerator.Current.Value;
                DbParameter parameter = GetDbProviderFactory(smartDatabaseType).CreateParameter();
                parameter.ParameterName = CreateParameterName(key, smartDatabaseType);
                if (value == SmartParameterDirection.Output)
                {
                    parameter.Direction = ParameterDirection.Output;
                }
                else if (value == SmartParameterDirection.ReturnValue)
                {
                    parameter.Direction = ParameterDirection.ReturnValue;
                }
                if (value == null)
                {
                    parameter.Value = DBNull.Value;
                }
                else
                {
                    parameter.Value = value;
                }
                parameter.Size = int.MaxValue;
                parameters.Add(parameter);
            }

            return parameters;
        }
        /// <summary>
        /// 根据键值对生成参数数组，默认是SqlServer数据库
        /// </summary>
        /// <param name="list">键值对</param>
        /// <returns>返回参数列表</returns>
        public static List<DbParameter> MappingOR(Dictionary<string, object> list)
        {
            return MappingOR(list, SmartDatabaseType.SqlServer);
        }
        #endregion
    }
    public static partial class SmartDatabase
    {
        #region 其他方法
        /// <summary>
        /// 根据数据库类型获得某种数据库系列对象创建工厂
        /// </summary>
        /// <param name="smartDatabaseType">数据库类型</param>
        /// <returns>创建工厂</returns>
        public static DbProviderFactory GetDbProviderFactory(SmartDatabaseType smartDatabaseType)
        {
            DbProviderFactory instance = null;
            switch (smartDatabaseType)
            {
                case SmartDatabaseType.SqlServer:
                    instance = SqlClientFactory.Instance;
                    break;
                case SmartDatabaseType.OleDb:
                    instance = OleDbFactory.Instance;
                    break;
                case SmartDatabaseType.Oracle:
                    instance = OracleClientFactory.Instance;
                    break;
            }
            return instance;
        }
        /// <summary>
        /// 根据列名创建参数名称
        /// </summary>
        /// <param name="columnName">列名</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        /// <returns>合法的参数名称</returns>
        public static string CreateParameterName(string columnName, SmartDatabaseType smartDatabaseType)
        {
            string parameterName = null;
            switch (smartDatabaseType)
            {
                case SmartDatabaseType.SqlServer:
                    parameterName = "@" + columnName.Trim().TrimStart(new char[] { '@' });
                    break;
                case SmartDatabaseType.Oracle:
                    parameterName = ":" + columnName.Trim().TrimStart(new char[] { ':' });
                    break;
                case SmartDatabaseType.OleDb:
                    parameterName = columnName.Trim();
                    break;
            }
            return parameterName;
        }
        /// <summary>
        /// 根据列名创建参数名称，默认是SqlServer数据库
        /// </summary>
        /// <param name="columnName">列名</param>
        /// <returns>合法的参数名称</returns>
        public static string CreateParameterName(string columnName)
        {
            return CreateParameterName(columnName, SmartDatabaseType.SqlServer);
        }
        /// <summary>
        /// 克隆参数数组
        /// </summary>
        /// <param name="parameters">源参数数据</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        /// <returns>克隆之后的参数数组</returns>
        public static DbParameter[] CloneDbParameters(DbParameter[] parameters, SmartDatabaseType smartDatabaseType)
        {
            DbParameter[] result = new DbParameter[parameters.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                result[i] = GetDbProviderFactory(smartDatabaseType).CreateParameter();
                result[i].ParameterName = parameters[i].ParameterName;
                result[i].Value = parameters[i].Value;
                result[i].Direction = parameters[i].Direction;
                result[i].DbType = parameters[i].DbType;
                result[i].SourceColumn = parameters[i].SourceColumn;
                result[i].SourceVersion = parameters[i].SourceVersion;
                result[i].Size = parameters[i].Size;
            }
            return result;
        }
        /// <summary>
        /// 克隆参数数组，默认是SqlServer数据库
        /// </summary>
        /// <param name="parameters">源参数数据</param>
        /// <returns>克隆之后的参数数组</returns>
        public static DbParameter[] CloneDbParameters(DbParameter[] parameters)
        {
            return CloneDbParameters(parameters, SmartDatabaseType.SqlServer);
        }
        /// <summary>
        /// 通过参数名在参数数组中查找参数
        /// </summary>
        /// <param name="parameters">参数数组</param>
        /// <param name="parameterName">参数名</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        /// <returns>找到的参数</returns>
        public static DbParameter FindDbParameter(DbParameter[] parameters, string parameterName, SmartDatabaseType smartDatabaseType)
        {
            parameterName = CreateParameterName(parameterName, smartDatabaseType);
            var linq = from parameter in parameters where parameter.ParameterName.ToLower().Trim() == parameterName.ToLower().Trim() select parameter;
            if (linq.Count() > 0)
            {
                return linq.First();
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 通过参数名在参数数组中查找参数，默认是SqlServer数据库
        /// </summary>
        /// <param name="parameters">参数数组</param>
        /// <param name="parameterName">参数名</param>
        /// <returns>找到的参数</returns>
        public static DbParameter FindDbParameter(DbParameter[] parameters, string parameterName)
        {
            return FindDbParameter(parameters, parameterName, SmartDatabaseType.SqlServer);
        }
        /// <summary>
        /// 通过参数名在参数数组中查找参数
        /// </summary>
        /// <param name="parameters">参数数组</param>
        /// <param name="parameterName">参数名</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        /// <returns>找到的参数</returns>
        public static DbParameter FindDbParameter(List<DbParameter> parameters, string parameterName, SmartDatabaseType smartDatabaseType)
        {
            return FindDbParameter(parameters.ToArray(), parameterName, smartDatabaseType);
        }
        /// <summary>
        /// 通过参数名在参数数组中查找参数，默认是SqlServer数据库
        /// </summary>
        /// <param name="parameters">参数数组</param>
        /// <param name="parameterName">参数名</param>
        /// <returns>找到的参数</returns>
        public static DbParameter FindDbParameter(List<DbParameter> parameters, string parameterName)
        {
            return FindDbParameter(parameters.ToArray(), parameterName, SmartDatabaseType.SqlServer);
        }
        /// <summary>
        /// 事务处理
        /// </summary>
        /// <param name="actions">要执行的操作</param>
        public static void TransactionScope(params Action[] actions)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                for (int i = 0; i < actions.Length; i++)
                {
                    actions[i].Invoke();
                }
                scope.Complete();
            }
        }
        /// <summary>
        /// 在纵向数据中查找值
        /// </summary>
        /// <param name="dataTable">纵向数据</param>
        /// <param name="keyColumn">作为键的列</param>
        /// <param name="valueColumn">作为值的列</param>
        /// <param name="itemName">查找的键</param>
        /// <param name="value">找到的值</param>
        /// <returns>是否找到</returns>
        public static bool FindValueInVerticalData(DataTable dataTable, string keyColumn, string valueColumn, string itemName, out object value)
        {
            var linq = from row in dataTable.AsEnumerable() where row.Field<string>(keyColumn).ToLower() == itemName.ToLower() select row.Field<object>(valueColumn);
            if (linq.Count() > 0)
            {
                value = linq.First();
                return true;
            }
            else
            {
                value = null;
                return false;
            }
        }
        /// <summary>
        /// 清除可能的SQL注入。建议使用参数的方式实现SQL操作。部分很难实现为参数方式的动态SQL，请使用此方法进行输入处理。清理过程不区分大小写。
        /// </summary>
        /// <param name="input">待处理的输入文本</param>
        /// <returns>处理后安全的输出文本</returns>
        public static string CleanInput(string input)
        {
            Regex keywordRegex = new Regex(@"\bexec\b|\binsert\b|\bdelete\b|\bupdate\b|\bdrop\b|\btruncate\b|\bselect\b|\bfrom\b|=|;|'|<>|!=|\balter\b|\bwhere\b|\bjoin\b", RegexOptions.IgnoreCase);

            return keywordRegex.Replace(input, "");
        }
        /// <summary>
        /// 按事务方式执行SmartSqlCommandLine命令列表
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="smartSqlCommandLineList">命令列表</param>
        /// <param name="smartDatabaseType">数据库类型</param>
        public static void ExecuteWithTransaction(string connectionString, List<SmartSqlCommandLine> smartSqlCommandLineList, SmartDatabaseType smartDatabaseType)
        {
            DbProviderFactory factory = GetDbProviderFactory(smartDatabaseType);
            using (DbConnection dbConnection = factory.CreateConnection())
            {
                dbConnection.ConnectionString = connectionString;
                dbConnection.Open();
                using (DbTransaction dbTransaction = dbConnection.BeginTransaction())
                {
                    try
                    {
                        for (int i = 0; i < smartSqlCommandLineList.Count; i++)
                        {
                            SmartSqlCommandLine smartSqlCommandLine = smartSqlCommandLineList[i];

                            using (DbCommand dbCommand = dbConnection.CreateCommand())
                            {
                                dbCommand.Transaction = dbTransaction;
                                dbCommand.CommandText = smartSqlCommandLine.SqlSentence;

                                //操作超时时间，目前设置为3分钟
                                dbCommand.CommandTimeout = 180;
                                if (smartSqlCommandLine.Parameters != null)
                                {
                                    dbCommand.Parameters.AddRange(smartSqlCommandLine.Parameters);
                                }
                                if (smartSqlCommandLine.SmartRollbackCondition != null)
                                {
                                    switch (smartSqlCommandLine.SmartRollbackCondition.RollbackConditionType)
                                    {
                                        case SmartRollbackConditionType.Custom:
                                            {
                                                dbCommand.ExecuteNonQuery();
                                                if (smartSqlCommandLine.SmartRollbackCondition.RollbackAction != null)
                                                {
                                                    try
                                                    {
                                                        smartSqlCommandLine.SmartRollbackCondition.RollbackAction();
                                                    }
                                                    catch
                                                    {
                                                        throw new Exception(smartSqlCommandLine.Error);
                                                    }
                                                }
                                            }
                                            break;
                                        case SmartRollbackConditionType.NotExists:
                                            {
                                                int result = Convert.ToInt32(dbCommand.ExecuteScalar());
                                                if (result == 0)
                                                {
                                                    throw new Exception(smartSqlCommandLine.Error);
                                                }
                                            }
                                            break;
                                        case SmartRollbackConditionType.NotHaveEffectiveRows:
                                            {
                                                int result = Convert.ToInt32(dbCommand.ExecuteNonQuery());
                                                if (result == 0)
                                                {
                                                    throw new Exception(smartSqlCommandLine.Error);
                                                }
                                            }
                                            break;
                                        case SmartRollbackConditionType.NotOnlyOne:
                                            {
                                                int result = Convert.ToInt32(dbCommand.ExecuteScalar());
                                                if (result == 1)
                                                {
                                                    throw new Exception(smartSqlCommandLine.Error);
                                                }
                                            }
                                            break;
                                    }
                                }
                                else
                                {
                                    dbCommand.ExecuteNonQuery();
                                }
                            }
                        }

                        dbTransaction.Commit();
                    }
                    catch (Exception err)
                    {
                        dbTransaction.Rollback();
                        throw new SmartRollbackException(err.Message);
                    }
                }
            }
        }
        #endregion
    }
}
